/***********************************************************************
 * Module:  DAOLocation.java
 * Author:  Simon
 * Purpose: Defines the Class DAOLocation
 ***********************************************************************/

package DAO;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.*;

import Modele.Location;

/** Data Access Object d'accès aux locations */
public class DAOLocation {
	/**
	 * Renvoie la liste de toutes les locations
	 * 
	 * 
	 * @return La liste de toutes les locations
	 */
	public static ArrayList<Location> getAllLocation() {
		Statement stat;
		ArrayList<Location> locations = new ArrayList<Location>();
		try {

			stat = DAO.getConnection().createStatement();
			stat.executeUpdate("use nemovelo");
			ResultSet res = stat.executeQuery("select * from Location");

			Location location;
			int idLocation, idVelo, idClient, idStationDebut, idStationFin;
			Date dateDeDebut, dateDeFin;
			while (res.next()) {

				idLocation = res.getInt("idLocation");
				idClient = res.getInt("idClient");
				idVelo = res.getInt("idVelo");
				idStationDebut = res.getInt("idStationDebut");
				idStationFin = res.getInt("idStationFin");
				dateDeDebut = res.getDate("dateDeDebut");
				dateDeFin = res.getDate("dateDeFin");

				location = new Location(idLocation, idClient, idVelo,
						idStationDebut, idStationFin, dateDeDebut, dateDeFin);

				locations.add(location);
			}

		} catch (SQLException e) {
			System.out.println("sql exception");
			while (e != null) {
				System.out.println(e.getErrorCode());
				System.out.println(e.getMessage());
				System.out.println(e.getSQLState());
				e.printStackTrace();
				e = e.getNextException();
			}
		}

		return locations;
	}

	/**
	 * Renvoie un stationnement en fonction de son identifiant
	 * 
	 * @param idLocation
	 *            Identifiant de la location demandée La location demandée
	 * @return La location demandée
	 */
	public static Location getLocationById(int idLocation) {
		Statement stat;
		Location location = null;

		try {

			stat = DAO.getConnection().createStatement();
			stat.executeUpdate("use nemovelo");
			ResultSet res = stat
					.executeQuery("select * from Location where idLocation="
							+ idLocation);

			int idVelo, idClient, idStationDebut, idStationFin;
			Date dateDeDebut, dateDeFin;
			if (res.next()) {

				idLocation = res.getInt("idLocation");
				idClient = res.getInt("idClient");
				idVelo = res.getInt("idVelo");
				idStationDebut = res.getInt("idStationDebut");
				idStationFin = res.getInt("idStationFin");
				dateDeDebut = res.getDate("dateDeDebut");
				dateDeFin = res.getDate("dateDeFin");

				location = new Location(idLocation, idClient, idVelo,
						idStationDebut, idStationFin, dateDeDebut, dateDeFin);

			}

		} catch (SQLException e) {
			System.out.println("sql exception");
			while (e != null) {
				System.out.println(e.getErrorCode());
				System.out.println(e.getMessage());
				System.out.println(e.getSQLState());
				e.printStackTrace();
				e = e.getNextException();
			}
		}
		return location;
	}

	/**
	 * Met à jour une location dans la base
	 * 
	 * @param location
	 *            L'agent de maintenance à mettre à jour
	 */
	public static void updateLocation(Location location) {
		PreparedStatement stat;
		try {
			stat = DAO.getConnection().prepareStatement(
					"select * from Location where idLocation=?",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			stat.setInt(1, location.getIdLocation());
			ResultSet res = stat.executeQuery();

			if (res.next()) {

				Integer idStationFin = null;
				if (location.getIdStationFin() != 0)
					idStationFin = location.getIdStationFin();

				res.updateInt("idClient", location.getIdClient());
				res.updateInt("idVelo", location.getIdVelo());
				res.updateInt("idStationDebut", location.getIdStationDebut());

				if (location.getIdStationFin() != 0)
					res.updateInt("idStationFin", idStationFin);
				else
					res.updateNull("idStationFin");

				res.updateDate("dateDeDebut", new java.sql.Date(location
						.getDateDeDebut().getTime()));

				if (location.getDateDeFin() != null)
					res.updateDate("dateDeFin", new java.sql.Date(location
							.getDateDeFin().getTime()));
				else
					res.updateNull("dateDeFin");

				res.updateRow();

			}

		} catch (SQLException e) {
			System.out.println("sql exception");
			while (e != null) {
				System.out.println(e.getErrorCode());
				System.out.println(e.getMessage());
				System.out.println(e.getSQLState());
				e.printStackTrace();
				e = e.getNextException();
			}
		}
	}

	/**
	 * Supprime une location de la base
	 * 
	 * @param location
	 *            L'agent de maintenance à supprimer
	 */
	public static void deleteLocation(Location location) {
		Statement stat;
		try {

			stat = DAO.getConnection().createStatement();

			stat.executeUpdate("delete from Location where idLocation="
					+ location.getIdLocation());

		} catch (SQLException e) {

			System.out.println("sql exception");
			while (e != null) {
				System.out.println(e.getErrorCode());
				System.out.println(e.getMessage());
				System.out.println(e.getSQLState());
				e.printStackTrace();
				e = e.getNextException();
			}
		}
	}

	/**
	 * Insère une nouvelle location dans la base
	 * 
	 * @param location
	 *            La location à insérer
	 */
	public static void insertLocation(Location location) {
		PreparedStatement stat;
		try {
			stat = DAO
					.getConnection()
					.prepareStatement(
							"insert into Location (idClient,idVelo,idStationDebut,idStationFin,dateDeDebut,dateDeFin) values (?,?,?,?,?,?)");

			stat.setInt(1, location.getIdClient());
			stat.setInt(2, location.getIdVelo());
			stat.setInt(3, location.getIdStationDebut());

			if (location.getIdStationFin() != 0)
				stat.setInt(4, location.getIdStationFin());
			else
				stat.setNull(4, Types.NULL);

			stat.setDate(5, new java.sql.Date(location.getDateDeDebut()
					.getTime()));

			if (location.getDateDeFin() != null)
				stat.setDate(6, new java.sql.Date(location.getDateDeFin()
						.getTime()));
			else
				stat.setNull(6, Types.NULL);

			stat.executeUpdate();

		} catch (SQLException e) {
			System.out.println("sql exception");
			while (e != null) {
				System.out.println(e.getErrorCode());
				System.out.println(e.getMessage());
				System.out.println(e.getSQLState());
				e.printStackTrace();
				e = e.getNextException();
			}
		}
	}

}